SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create procedure [dbo].[SP_TRLIQU_RECI_Q01]
/*------------------------------------------------------*/
/*-- Empresa		: OFISIS S.A.			*/
/*-- Cliente		: SANTILLANA			*/
/*-- Sistema		: Tesoreria			*/
/*-- Programa		: Reporte Recibos no utilizados	*/
/*-- Script		: SP_TRLIQU_RECI_Q01		*/
/*-- Nombre		: SP_TRLIQU_RECI_Q01		*/
/*-- Desarrollado	: Denis Villacres		*/
/*-- Fecha		: 07/05/2001			*/
/*-- Base de Datos	: Oracle			*/
/*-- Version		: 8.0				*/
/*------------------------------------------------------*/

/*------------------------------------------------------*/
/*-- Modificado por	 : D.V.P.			*/
/*-- Fecha modificacisn	 : 01/06/2001			*/
/*-- Detalle modificacisn: migracion a SQL		*/
/*------------------------------------------------------*/
/*------------------------------------------------------*/
/*-- Modificado por	 :				*/
/*-- Fecha modificacisn	 :				*/
/*-- Detalle modificacisn:				*/
/*------------------------------------------------------*/
/*------------------------------------------------------*/
/* drop proc SP_TRLIQU_RECI_Q01				*/
/* grant all on SP_TRLIQU_RECI_Q01 to public		*/
/* Execute SP_TRLIQU_RECI_Q01 '99'			*/
/*------------------------------------------------------*/

@ISCO_EMPR TD_VC_002

As
Begin
Declare
 @CSCO_LIQU		TD_VC_020,
 @CSNU_INIC		TD_VC_010,
 @CSNU_FINA		TD_VC_010,
 @VSNU_RECI_MAX1   	TD_VC_010,
 @VSNU_RECI_MAX2   	TD_VC_010,
 @VSNU_RECI_MAX3   	TD_VC_010,
 @VSNU_RECI_MAX4   	TD_VC_010,
 @VSNU_RECI_MAX5   	TD_VC_010,
 @VSNU_RECI_MAX6   	TD_VC_010,
 @VNNU_RECI_MAX1   	TD_IN_001,
 @VNNU_RECI_MAX2   	TD_IN_001,
 @VNNU_RECI_MAX3   	TD_IN_001,
 @VNNU_RECI_MAX4   	TD_IN_001,
 @VNNU_RECI_MAX5   	TD_IN_001,
 @VNNU_RECI_MAX6   	TD_IN_001,
 @VNNU_CONT		TD_IN_001,
 @VNNU_RECI_MAXI	TD_IN_001,
 @VNNU_CON1		TD_IN_001,
 @VNNU_CON2		TD_IN_001,
 @VNNU_CON3		TD_IN_001,
 @VNNU_CON4		TD_IN_001,
 @VNNU_CON5		TD_IN_001,
 @VNNU_CON6		TD_IN_001,
 @VSNU_RECI_NOUT   	TD_VC_010

Declare CU_TRLIQU_RECI_Q01_1 Cursor For
Select   CO_LIQU, NU_RECI_INIC, NU_RECI_FINA
From 	 TRLIQU_RECI
Where    CO_EMPR = @ISCO_EMPR
Order By CO_LIQU

BEGIN

select @VNNU_CON1 = 0
select @VNNU_CON2 = 0
select @VNNU_CON3 = 0
select @VNNU_CON4 = 0
select @VNNU_CON5 = 0
select @VNNU_CON6 = 0


	   Open CU_TRLIQU_RECI_Q01_1
	   Fetch CU_TRLIQU_RECI_Q01_1 
	   Into @CSCO_LIQU, @CSNU_INIC, @CSNU_FINA
		While (@@FETCH_STATUS = 0)
		Begin
	 	If (@@FETCH_STATUS < 0)
   		Begin
    		 --  RAISERROR 20001 "FIN DE ARCHIVO"
    		   Close CU_TRLIQU_RECI_Q01_1
    		   Deallocate CU_TRLIQU_RECI_Q01_1
    		   Return
   		End


		Select  @VSNU_RECI_MAX1 = Max(T2.NU_RECI_COBR)
	    	From    TCPLAN_LIQU T1, TDPLAN_LIQU T2
	    	Where   T1.CO_EMPR = @ISCO_EMPR
		    	AND T1.CO_EMPR = T2.CO_EMPR
	            	AND T1.NU_PLAN = T2.NU_PLAN
	            	And T1.CO_LIQU = @CSCO_LIQU
		    	And T2.NU_RECI_COBR >= @CSNU_INIC
		    	And T2.NU_RECI_COBR <= @CSNU_FINA

	    	Select  @VSNU_RECI_MAX2 = Max(NU_RECI_COBR)
	    	From    TDMOVI_BANC
	    	Where   CO_EMPR = @ISCO_EMPR
	            	And CO_LIQU = @CSCO_LIQU
		    	And NU_RECI_COBR >= @CSNU_INIC
		    	And NU_RECI_COBR <= @CSNU_FINA

	    	Select  @VSNU_RECI_MAX3 = Max(NU_RECI)
	    	From    TRLIRE_ANUL
	    	Where   CO_EMPR = @ISCO_EMPR
	            	And CO_LIQU = @CSCO_LIQU
		    	And NU_RECI >= @CSNU_INIC
		    	And NU_RECI <= @CSNU_FINA

	  	Select 	@VSNU_RECI_MAX4 = Max(T1.NU_RECI_COBR)
		From 	TCMOVI_BANC T1, TRLIQU_RECI T2, TMCOBR T4
		Where 	T1.CO_EMPR = @ISCO_EMPR
			And T1.NU_RECI_COBR Is Not Null 
			And T1.NU_RECI_COBR >= T2.NU_RECI_INIC
			And T1.NU_RECI_COBR <= T2.NU_RECI_FINA
			And T1.NU_RECI_COBR >= @CSNU_INIC
			And T1.NU_RECI_COBR <= @CSNU_FINA
			And T1.CO_EMPR  = T2.CO_EMPR 
			And T2.CO_EMPR  = T4.CO_EMPR 
			And T2.CO_LIQU  = T4.CO_LIQU
			And T1.CO_EMPR = T4.CO_EMPR
			And T2.CO_LIQU = @CSCO_LIQU


	  	Select 	@VSNU_RECI_MAX5 = Max(T1.NU_RECI_COBR)

		From 	TCMOVI_CAJA T1, TRLIQU_RECI T2, TMCOBR T4
		Where 	T1.CO_EMPR = @ISCO_EMPR
			And T1.NU_RECI_COBR Is Not Null 
			And T1.NU_RECI_COBR >= T2.NU_RECI_INIC
			And T1.NU_RECI_COBR <= T2.NU_RECI_FINA
			And T1.NU_RECI_COBR >= @CSNU_INIC
			And T1.NU_RECI_COBR <= @CSNU_FINA
			And T1.CO_EMPR  = T2.CO_EMPR 
			And T2.CO_EMPR  = T4.CO_EMPR 
			And T2.CO_LIQU  = T4.CO_LIQU
			And T1.CO_EMPR = T4.CO_EMPR
			And T2.CO_LIQU = @CSCO_LIQU


	    	Select  @VSNU_RECI_MAX6 = Max(NU_RECI_COBR)
	    	From    TDMOVI_CAJA
	    	Where   CO_EMPR = @ISCO_EMPR
	            	And CO_LIQU = @CSCO_LIQU
		    	And NU_RECI_COBR >= @CSNU_INIC
		    	And NU_RECI_COBR <= @CSNU_FINA


--***Conversion a numericos
If Rtrim(Ltrim(@VSNU_RECI_MAX1)) Is Null 
	Select @VNNU_RECI_MAX1 = 0
else 
	Select @VNNU_RECI_MAX1 = Convert(Integer, @VSNU_RECI_MAX1)


If Rtrim(Ltrim(@VSNU_RECI_MAX2)) Is Null 
	Select @VNNU_RECI_MAX2 = 0
else 
	Select @VNNU_RECI_MAX2 = Convert(Integer, @VSNU_RECI_MAX2)


If Rtrim(Ltrim(@VSNU_RECI_MAX3)) Is Null
	Select @VNNU_RECI_MAX3 = 0
else 
	Select @VNNU_RECI_MAX3 = Convert(Integer, @VSNU_RECI_MAX3)


If Rtrim(Ltrim(@VSNU_RECI_MAX4)) Is Null
	Select @VNNU_RECI_MAX4 = 0
else 
	Select @VNNU_RECI_MAX4 = Convert(Integer, @VSNU_RECI_MAX4)


If Rtrim(Ltrim(@VSNU_RECI_MAX5)) Is Null
	Select @VNNU_RECI_MAX5 = 0
else 
	Select @VNNU_RECI_MAX5 = Convert(Integer, @VSNU_RECI_MAX5)


If Rtrim(Ltrim(@VSNU_RECI_MAX6)) Is Null
	Select @VNNU_RECI_MAX6 = 0
else 
	Select @VNNU_RECI_MAX6 = Convert(Integer, @VSNU_RECI_MAX6)


--***Busca el Nro. mayor de los recibos utilizados
	If @VNNU_RECI_MAX1 >= @VNNU_RECI_MAX2
	   Select @VNNU_RECI_MAXI = @VNNU_RECI_MAX1
	Else 
	   Select @VNNU_RECI_MAXI = @VNNU_RECI_MAX2


	If @VNNU_RECI_MAXI < @VNNU_RECI_MAX3
	   Select @VNNU_RECI_MAXI = @VNNU_RECI_MAX3

	If @VNNU_RECI_MAXI < @VNNU_RECI_MAX4
	   Select @VNNU_RECI_MAXI = @VNNU_RECI_MAX4

	If @VNNU_RECI_MAXI < @VNNU_RECI_MAX5
	   Select @VNNU_RECI_MAXI = @VNNU_RECI_MAX5

	If @VNNU_RECI_MAXI < @VNNU_RECI_MAX6
	   Select @VNNU_RECI_MAXI = @VNNU_RECI_MAX6


--*** Se Inicializa el contador igual al Nro menor del cursor
Select @VNNU_CONT = Convert(Integer, @CSNU_INIC)

	If @VNNU_RECI_MAXI != 0

	While @VNNU_CONT < @VNNU_RECI_MAXI
	BEGIN
		Select @VSNU_RECI_NOUT = Replicate('0',10-Len(Convert(Varchar,@VNNU_CONT)))+Convert(Varchar,@VNNU_CONT)

	    	    Select  @VNNU_CON1 = Count(*)
	    	    From    TCPLAN_LIQU T1, TDPLAN_LIQU T2
	            Where   T1.CO_EMPR = @ISCO_EMPR
		      	    AND T1.CO_EMPR = T2.CO_EMPR
	            	    AND T1.NU_PLAN = T2.NU_PLAN
	            	    And T1.CO_LIQU = @CSCO_LIQU
		      	    And T2.NU_RECI_COBR = @VSNU_RECI_NOUT

	    	    Select  @VNNU_CON2 = Count(*)
	            From    TDMOVI_BANC
	            Where   CO_EMPR = @ISCO_EMPR
	                    And CO_LIQU = @CSCO_LIQU
		      	    And NU_RECI_COBR = @VSNU_RECI_NOUT

	    	    Select  @VNNU_CON3 = Count(*)
	    	    From    TRLIRE_ANUL
	     	    Where   CO_EMPR = @ISCO_EMPR
	            	    And CO_LIQU = @CSCO_LIQU
		      	    And NU_RECI = @VSNU_RECI_NOUT

	    	    Select  @VNNU_CON4 = Count(*)
		    From    TCMOVI_BANC T1, TRLIQU_RECI T2, TMCOBR T4
	            Where   T1.CO_EMPR = @ISCO_EMPR
			    And T1.NU_RECI_COBR Is Not Null 
			    And T1.NU_RECI_COBR >= T2.NU_RECI_INIC
			    And T1.NU_RECI_COBR <= T2.NU_RECI_FINA
			    And T1.CO_EMPR  = T2.CO_EMPR 
			    And T2.CO_EMPR  = T4.CO_EMPR 
			    And T2.CO_LIQU  = T4.CO_LIQU
			    And T1.CO_EMPR = T4.CO_EMPR
			    And T2.CO_LIQU = @CSCO_LIQU
	      	    	    And T1.NU_RECI_COBR = @VSNU_RECI_NOUT

	    	    Select  @VNNU_CON5 = Count(*)
		    From    TCMOVI_CAJA T1, TRLIQU_RECI T2, TMCOBR T4
	            Where   T1.CO_EMPR = @ISCO_EMPR
			    And T1.NU_RECI_COBR Is Not Null 
			    And T1.NU_RECI_COBR >= T2.NU_RECI_INIC
			    And T1.NU_RECI_COBR <= T2.NU_RECI_FINA
			    And T1.CO_EMPR  = T2.CO_EMPR 			    And T2.CO_EMPR  = T4.CO_EMPR 
			    And T2.CO_LIQU  = T4.CO_LIQU
			    And T1.CO_EMPR = T4.CO_EMPR
			    And T2.CO_LIQU = @CSCO_LIQU
	      	    	    And T1.NU_RECI_COBR = @VSNU_RECI_NOUT


	    	    Select  @VNNU_CON6 = Count(*)
	            From    TDMOVI_CAJA
	            Where   CO_EMPR = @ISCO_EMPR
	                    And CO_LIQU = @CSCO_LIQU
		      	    And NU_RECI_COBR = @VSNU_RECI_NOUT

		If (@VNNU_CON1 + @VNNU_CON2 + @VNNU_CON3 + @VNNU_CON4 + @VNNU_CON5 + @VNNU_CON6 = 0)
		   Insert Into #TWLIQU_RECI_Q01_1(CO_EMPR, CO_LIQU,
			NU_RECI_INIC, NU_RECI_FINA, NU_RECI_NOUT)
		   Values (@ISCO_EMPR, @CSCO_LIQU, @CSNU_INIC, @CSNU_FINA,
			@VSNU_RECI_NOUT)

		   If @@error <> 0
		      Begin
		        Rollback tran
			Return
		      End  

		Select @VNNU_CONT = @VNNU_CONT + 1

	END
  Fetch CU_TRLIQU_RECI_Q01_1 
  Into @CSCO_LIQU, @CSNU_INIC, @CSNU_FINA

END

  Close CU_TRLIQU_RECI_Q01_1
  Deallocate CU_TRLIQU_RECI_Q01_1

END
end
/*------------------------------ FIN ---------------------------------*/


GO
